#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--创建请假表
CREATE TABLE hive.$1zx_rpt.rpt_leave as
WITH tmp as (
SELECT
    class_date,
    class_id,
    SUM(type_m) AS type_m_sum,
    SUM(type_a) AS type_a_sum,
    SUM(type_e) AS type_e_sum

FROM hive.$1zx_dws.dws_leava
GROUP BY GROUPING SETS
(class_date),
(class_date, class_id),
(class_date, class_id, type_m),
(class_date, class_id, type_a),
(class_date, class_id, type_e)
)
SELECT
    class_date,
    tmp.class_id,
    type_m_sum,
    CAST((type_m_sum * 1.0000 / studying_student_count )*100 as DECIMAL(5,2)) as m_percentage,
    type_a_sum,
    CAST((type_a_sum *1.0000 / studying_student_count)*100  as DECIMAL(5,2)) as a_percentage ,
    type_e_sum,
    CAST((type_e_sum *1.0000  / studying_student_count)*100 as DECIMAL(5,2)) as e_percentage

FROM tmp
JOIN hive.$1zx_dwd.dim_class_studying_student_count c
ON tmp.class_date = c.studying_date AND tmp.class_id = c.class_id;
-- 创建打卡正常表
CREATE TABLE hive.$1zx_rpt.rpt_signin as
SELECT
    s.class_id,
    signin_date,
    time_type,
    total_amount,
    studying_student_count,
    CAST((total_amount * 1.0000 / c.studying_student_count)*100 as DECIMAL(5,2)) as percentage
FROM hive.$1zx_dws.dws_signin_2  s
JOIN hive.$1zx_dwd.dim_class_studying_student_count c
ON c.class_id = s.class_id AND c.studying_date = s.signin_date
WHERE time_type = 'morning' OR time_type = 'afternoon' OR time_type = 'evening';


--创建打卡迟到表
CREATE TABLE hive.$1zx_rpt.rpt_signin_last as
SELECT
    s.class_id,
    signin_date,
    time_type,
    total_amount,
    studying_student_count,
    CAST((total_amount * 1.0000 / c.studying_student_count)*100 as DECIMAL(5,2)) as percentage
FROM hive.$1zx_dws.dws_signin_2  s
JOIN hive.$1zx_dwd.dim_class_studying_student_count c
ON c.class_id = s.class_id AND c.studying_date = s.signin_date
WHERE time_type = 'morning_last' OR time_type = 'afternoon_last' OR time_type = 'evening_last';
--创建旷课表
CREATE TABLE hive.$1zx_rpt.rpt_truant as
WITH tmp as (
SELECT
    s.class_id,
    signin_date,
    time_type,
    total_amount,
    CASE WHEN time_type = 'morning' AND type_m_sum =1 THEN (total_amount + type_m_sum)
    WHEN time_type = 'afternoon' AND type_a_sum =1 THEN (total_amount + type_a_sum)
    WHEN time_type = 'evening' AND type_e_sum =1 THEN (total_amount + type_e_sum)
    ELSE total_amount
    END as amount,
    studying_student_count

FROM hive.$1zx_rpt.rpt_signin  s
LEFT JOIN hive.$1zx_rpt.rpt_leave r
ON s.signin_date = r.class_date AND s.class_id = r.class_id
)
SELECT
    class_id,
    signin_date,
    time_type,
    amount,
    CAST( (amount *1.0000 / studying_student_count)*100 as  DECIMAL(5,2)) as attendance_rate
FROM tmp;
"